﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace CRM.Business_Object
{
    public class CustomerContactObject
    {
        public string CustomerName { get; set; }
        public string Title { get; set; }
        public string AccountName { get; set; }
        public int OfficePhone { get; set; }
        public int Mobile { get; set; }
        public int Fax { get; set; }
        public int HomePhone { get; set; }
        public string AssignedTo { get; set; }
        public string Email { get; set; }
        public string PrimaryAddress { get; set; }
        public string PrimaryCity { get; set; }
        public string PrimaryState { get; set; }
        public int PrimaryPostalCode { get; set; }
        public string PrimaryCountry { get; set; }
        public string OtherAdress { get; set; }
        public string OtherCity { get; set; }
        public string OtherState { get; set; }
        public int OtherPostalCode { get; set; }
        public string OtherCountry { get; set; }


        public static void createCustomerContacts(CustomerContactObject c)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=CustomerContacts;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Insert into Contacts(CustomerName,Title,AccountName,OfficePhone,Mobile,Fax,HomePhone,AssignedTo,Email,PrimaryAddress,PrimaryCity,PrimaryState,PrimaryPostalCode,PrimaryCountry,OtherAddress,OtherCity,OtherState,OtherPostalCode,OtherCountry) Values(@CustomerName,@Title,@AccountName,@OfficePhone,@Mobile,@Fax,@HomePhone,@AssignedTo,@Email,@PrimaryAddress,@PrimaryCity,@PrimaryState,@PrimaryPostalCode,@PrimaryCountry,@OtherAddress,@OtherCity,@OtherState,@OtherPostalCode,@OtherCountry)";
                comm.Parameters.AddWithValue("@CustomerName", c.CustomerName);
                comm.Parameters.AddWithValue("@Title", c.Title);
                comm.Parameters.AddWithValue("@AccountName", c.AccountName);
                comm.Parameters.AddWithValue("@OfficePhone", c.OfficePhone);
                comm.Parameters.AddWithValue("@Mobile",c.Mobile);
                comm.Parameters.AddWithValue("@Fax", c.Fax);
                comm.Parameters.AddWithValue("@HomePhone", c.HomePhone);
                comm.Parameters.AddWithValue("@AssignedTo", c.AssignedTo);
                comm.Parameters.AddWithValue("@Email", c.Email);
                comm.Parameters.AddWithValue("@PrimaryAddress", c.PrimaryAddress);
                comm.Parameters.AddWithValue("@PrimaryCity", c.PrimaryCity);
                comm.Parameters.AddWithValue("@PrimaryState", c.PrimaryState);
                comm.Parameters.AddWithValue("@PrimaryPostalCode", c.PrimaryPostalCode);
                comm.Parameters.AddWithValue("@PrimaryCountry", c.PrimaryCountry);
                comm.Parameters.AddWithValue("@OtherAddress", c.OtherAdress);
                comm.Parameters.AddWithValue("@OtherCity", c.OtherCity);
                comm.Parameters.AddWithValue("@OtherState", c.OtherState);
                comm.Parameters.AddWithValue("@OtherPostalCode", c.OtherPostalCode);
                comm.Parameters.AddWithValue("@OtherCountry", c.OtherCountry);

                int rows = comm.ExecuteNonQuery();
                if (rows < 0)
                {

                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable viewCustomerContacts()
        {
            SqlConnection conn = null;
            DataTable dt = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=CustomerContacts;Integrated security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select CustomerName,Title,AccountName,Email,OfficePhone,PrimaryAddress,PrimaryCity from Contacts";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static void updateCustomerContacts(CustomerContactObject b)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=CustomerContacts;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Update Contacts set Title=@Title," + "AccountName=@AccountName," + "Email=@Email where CustomerName=@CustomerName";
                comm.Parameters.AddWithValue("@CustomerName", b.CustomerName);
                comm.Parameters.AddWithValue("@Title", b.Title);
                comm.Parameters.AddWithValue("@AccountName", b.AccountName);
                comm.Parameters.AddWithValue("@Email",b.Email);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        public static void deleteCustomerContacts(CustomerContactObject b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=CustomerContacts;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Contacts WHERE CustomerName=@CustomerName";
                comm.Parameters.AddWithValue("@CustomerName", b.CustomerName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}